﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

/// <summary>
/// QuangNN - File Helper hỗ trợ xây dựng file Excel bằng thư viện NPOI
/// </summary>
public class NpoiHelper
{
    private const string FONT_NAME = "Times New Roman";
    private const short FONT_SIZE = 13;

    private HSSFWorkbook workbook { get; set; }

    //Cell font
    public IFont GetFont(string fontName = FONT_NAME, short fontSize = FONT_SIZE, bool bold = false, bool italic = false)
    {
        IFont font = this.workbook.CreateFont();
        font.FontName = fontName;

        if (bold)
        {
            font.Boldweight = (short)FontBoldWeight.BOLD;
        }

        font.IsItalic = italic;

        font.FontHeightInPoints = fontSize;

        return font;
    }

    //Cell style
    public ICellStyle GetCellStyle(HorizontalAlignment align = HorizontalAlignment.LEFT, VerticalAlignment vAlign = VerticalAlignment.CENTER, string fontName = FONT_NAME, short fontSize = FONT_SIZE, bool bold = false, bool italic = false, short dataFormat = 0)
    {
        ICellStyle cellStyle = this.workbook.CreateCellStyle();
        cellStyle.SetFont(this.GetFont(fontName, fontSize, bold, italic));
        cellStyle.Alignment = align;
        cellStyle.VerticalAlignment = vAlign;
        cellStyle.DataFormat = dataFormat;
        return cellStyle;
    }
    //Cell warp
    public ICellStyle GetCellStyleWrap(HorizontalAlignment align = HorizontalAlignment.CENTER, VerticalAlignment vAlign = VerticalAlignment.CENTER, string fontName = FONT_NAME, short fontSize = FONT_SIZE, bool bold = false, bool italic = false, short dataFormat = 0)
    {
        ICellStyle cellStyle = this.workbook.CreateCellStyle();
        cellStyle.SetFont(this.GetFont(fontName, fontSize, bold, italic));
        cellStyle.Alignment = align;
        cellStyle.VerticalAlignment = vAlign;
        cellStyle.DataFormat = dataFormat;
        cellStyle.WrapText = true;
        return cellStyle;
    }

    //Fonts
    public IFont NormalFont;
    public IFont BoldFont;
    public IFont ItalicFont;
    public IFont BoldItalicFont;

    //Border style    
    private ICellStyle BorderTopStyle;
    private ICellStyle BorderLeftStyle;
    private ICellStyle BorderRightStyle;
    private ICellStyle BorderBottomStyle;

    private ICellStyle BorderTopLeftStyle;
    private ICellStyle BorderTopRightStyle;
    private ICellStyle BorderBottomLeftStyle;
    private ICellStyle BorderBottomRightStyle;

    private ICellStyle BorderTopLeftRightStyle;
    private ICellStyle BorderBottomLeftRightStyle;
    private ICellStyle BorderLeftRightStyle;

    private ICellStyle BorderLeftTopBottomStyle;
    private ICellStyle BorderRightTopBottomStyle;
    private ICellStyle BorderTopBottomStyle;

    private ICellStyle BorderAllStyle;

    private ICellStyle StyleWarp;


    //Normal Cell style
    private ICellStyle NormalStyle;
    private ICellStyle NormalCenterStyle;
    private ICellStyle NormalRightStyle;

    //TableCell style
    private ICellStyle TableCellStyle;
    private ICellStyle TableCellCenterStyle;
    private ICellStyle TableCellRightStyle;

    //Table Header style    
    private ICellStyle TableHeaderStyle;
    private ICellStyle TableHeaderCenterStyle;
    private ICellStyle TableHeaderRightStyle;

    //ReportHeader style
    private ICellStyle ReportHeaderStyle;
    private ICellStyle ReportrHeaderCenteStyle;
    private ICellStyle ReportHeaderRightStyle;

    //ReportTitle style
    private ICellStyle ReportTitleStyle;

    //ReportTitleParam style    
    private ICellStyle ReportTitleParamStyle;
    private ICellStyle ReportTitleParamCenterStyle;
    private ICellStyle ReportTitleParamRightStyle;

    //Italic style    
    private ICellStyle ItalicStyle;
    private ICellStyle ItalicCenterStyle;
    private ICellStyle ItalicRightStyle;

    public static void SetBoderForStyle(ICellStyle style)
    {
        style.BorderTop = BorderStyle.THIN;
        style.BorderLeft = BorderStyle.THIN;
        style.BorderRight = BorderStyle.THIN;
        style.BorderBottom = BorderStyle.THIN;

    }

    //
    // TODO: Add constructor logic here
    //
    public NpoiHelper(HSSFWorkbook workbook)
    {
        this.workbook = workbook;

        //Fonts
        NormalFont = GetFont(FONT_NAME, FONT_SIZE);
        BoldFont = GetFont(FONT_NAME, FONT_SIZE, true);
        ItalicFont = GetFont(FONT_NAME, FONT_SIZE, false, true);
        BoldItalicFont = GetFont(FONT_NAME, FONT_SIZE, true, true);

        //Border style
        BorderTopStyle = workbook.CreateCellStyle();
        BorderTopStyle.BorderTop = BorderStyle.THIN;

        BorderLeftStyle = workbook.CreateCellStyle();
        BorderLeftStyle.BorderLeft = BorderStyle.THIN;

        BorderRightStyle = workbook.CreateCellStyle();
        BorderRightStyle.BorderRight = BorderStyle.THIN;

        BorderBottomStyle = workbook.CreateCellStyle();
        BorderBottomStyle.BorderBottom = BorderStyle.THIN;

        BorderTopLeftStyle = workbook.CreateCellStyle();
        BorderTopLeftStyle.BorderTop = BorderStyle.THIN;
        BorderTopLeftStyle.BorderLeft = BorderStyle.THIN;

        BorderTopRightStyle = workbook.CreateCellStyle();
        BorderTopRightStyle.BorderTop = BorderStyle.THIN;
        BorderTopRightStyle.BorderRight = BorderStyle.THIN;

        BorderBottomLeftStyle = workbook.CreateCellStyle();
        BorderBottomLeftStyle.BorderBottom = BorderStyle.THIN;
        BorderBottomLeftStyle.BorderLeft = BorderStyle.THIN;

        BorderBottomRightStyle = workbook.CreateCellStyle();
        BorderBottomRightStyle.BorderBottom = BorderStyle.THIN;
        BorderBottomRightStyle.BorderRight = BorderStyle.THIN;

        BorderTopLeftRightStyle = workbook.CreateCellStyle();
        BorderTopLeftRightStyle.BorderTop = BorderStyle.THIN;
        BorderTopLeftRightStyle.BorderLeft = BorderStyle.THIN;
        BorderTopLeftRightStyle.BorderRight = BorderStyle.THIN;

        BorderBottomLeftRightStyle = workbook.CreateCellStyle();
        BorderBottomLeftRightStyle.BorderBottom = BorderStyle.THIN;
        BorderBottomLeftRightStyle.BorderLeft = BorderStyle.THIN;
        BorderBottomLeftRightStyle.BorderRight = BorderStyle.THIN;

        BorderLeftRightStyle = workbook.CreateCellStyle();
        BorderLeftRightStyle.BorderLeft = BorderStyle.THIN;
        BorderLeftRightStyle.BorderRight = BorderStyle.THIN;

        BorderLeftTopBottomStyle = workbook.CreateCellStyle();
        BorderLeftTopBottomStyle.BorderLeft = BorderStyle.THIN;
        BorderLeftTopBottomStyle.BorderTop = BorderStyle.THIN;
        BorderLeftTopBottomStyle.BorderBottom = BorderStyle.THIN;

        BorderRightTopBottomStyle = workbook.CreateCellStyle();
        BorderRightTopBottomStyle.BorderRight = BorderStyle.THIN;
        BorderRightTopBottomStyle.BorderTop = BorderStyle.THIN;
        BorderRightTopBottomStyle.BorderBottom = BorderStyle.THIN;

        BorderTopBottomStyle = workbook.CreateCellStyle();
        BorderTopBottomStyle.BorderTop = BorderStyle.THIN;
        BorderTopBottomStyle.BorderBottom = BorderStyle.THIN;

        BorderAllStyle = workbook.CreateCellStyle();
        SetBoderForStyle(BorderAllStyle);

        //NormalCell style
        NormalStyle = GetCellStyle(HorizontalAlignment.LEFT, VerticalAlignment.CENTER, FONT_NAME, FONT_SIZE, false, false);
        NormalCenterStyle = GetCellStyle(HorizontalAlignment.CENTER, VerticalAlignment.CENTER, FONT_NAME, FONT_SIZE, false, false);
        NormalRightStyle = GetCellStyle(HorizontalAlignment.RIGHT, VerticalAlignment.CENTER, FONT_NAME, FONT_SIZE, false, false);

        //TableCell style
        TableCellStyle = GetCellStyle(HorizontalAlignment.LEFT, VerticalAlignment.CENTER, FONT_NAME, FONT_SIZE, false, false);
        SetBoderForStyle(TableCellStyle);

        TableCellCenterStyle = GetCellStyle(HorizontalAlignment.CENTER, VerticalAlignment.CENTER, FONT_NAME, FONT_SIZE, false, false);
        SetBoderForStyle(TableCellCenterStyle);

        TableCellRightStyle = GetCellStyle(HorizontalAlignment.RIGHT, VerticalAlignment.CENTER, FONT_NAME, FONT_SIZE, false, false, 3);
        SetBoderForStyle(TableCellRightStyle);

        StyleWarp = GetCellStyleWrap(HorizontalAlignment.CENTER, VerticalAlignment.CENTER, FONT_NAME, FONT_SIZE, true, false);
        SetBoderForStyle(StyleWarp);
        //Table Header style    
        TableHeaderStyle = GetCellStyle(HorizontalAlignment.LEFT, VerticalAlignment.CENTER, FONT_NAME, FONT_SIZE, true, false);
        SetBoderForStyle(TableHeaderStyle);

        TableHeaderCenterStyle = GetCellStyle(HorizontalAlignment.CENTER, VerticalAlignment.CENTER, FONT_NAME, FONT_SIZE, true, false);
        SetBoderForStyle(TableHeaderCenterStyle);

        TableHeaderRightStyle = GetCellStyle(HorizontalAlignment.RIGHT, VerticalAlignment.CENTER, FONT_NAME, FONT_SIZE, true, false);
        SetBoderForStyle(TableHeaderRightStyle);

        //ReportHeader style
        ReportHeaderStyle = GetCellStyle(HorizontalAlignment.LEFT, VerticalAlignment.CENTER, FONT_NAME, FONT_SIZE, true, false);
        ReportrHeaderCenteStyle = GetCellStyle(HorizontalAlignment.CENTER, VerticalAlignment.CENTER, FONT_NAME, FONT_SIZE, true, false);
        ReportHeaderRightStyle = GetCellStyle(HorizontalAlignment.RIGHT, VerticalAlignment.CENTER, FONT_NAME, FONT_SIZE, true, false);

        //ReportTitle style
        ReportTitleStyle = GetCellStyle(HorizontalAlignment.CENTER, VerticalAlignment.CENTER, FONT_NAME, FONT_SIZE + 4, true, false);

        //ReportTitleParam style    
        ReportTitleParamStyle = GetCellStyle(HorizontalAlignment.LEFT, VerticalAlignment.CENTER, FONT_NAME, FONT_SIZE, true, false);
        ReportTitleParamCenterStyle = GetCellStyle(HorizontalAlignment.CENTER, VerticalAlignment.CENTER, FONT_NAME, FONT_SIZE, true, false);
        ReportTitleParamRightStyle = GetCellStyle(HorizontalAlignment.RIGHT, VerticalAlignment.CENTER, FONT_NAME, FONT_SIZE, true, false);

        //Italic style    
        ItalicStyle = GetCellStyle(HorizontalAlignment.LEFT, VerticalAlignment.CENTER, FONT_NAME, FONT_SIZE, false, true);
        ItalicCenterStyle = GetCellStyle(HorizontalAlignment.CENTER, VerticalAlignment.CENTER, FONT_NAME, FONT_SIZE, false, true);
        ItalicRightStyle = GetCellStyle(HorizontalAlignment.RIGHT, VerticalAlignment.CENTER, FONT_NAME, FONT_SIZE, false, true);
    }

    //Get Row
    public static IRow GetRow(ISheet sheet, int rowIndex)
    {
        IRow result = sheet.GetRow(rowIndex);

        if (result == null)
        {
            result = sheet.CreateRow(rowIndex);
        }

        return result;
    }

    //Get Cell
    public static ICell GetCell(IRow row, int colIndex)
    {
        ICell result = row.GetCell(colIndex);

        if (result == null)
        {
            result = row.CreateCell(colIndex);
        }

        return result;
    }

    //Get Cell & Merged Region
    public ICell GetCell(ISheet sheet, IRow row, int colIndex, int firstRow, int lastRow, int firstCol, int lastCol, IFont font, short dataFormat = 0)
    {
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.AddMergedRegion(region);

        ICell cell = GetCell(row, colIndex);
        cell.CellStyle.SetFont(font);
        cell.CellStyle.DataFormat = dataFormat;
        return cell;
    }

    //Get Cell Right & Merged Region
    public ICell GetCellRight(ISheet sheet, IRow row, int colIndex, int firstRow, int lastRow, int firstCol, int lastCol, IFont font, short dataFormat = 3)
    {
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.AddMergedRegion(region);

        ICell cell = GetCell(row, colIndex);
        cell.CellStyle.SetFont(font);
        cell.CellStyle.Alignment = HorizontalAlignment.RIGHT;
        cell.CellStyle.DataFormat = dataFormat;
   
        return cell;
    }

    //Get Cell Center & Merged Region
    public ICell GetCellCenter(ISheet sheet, IRow row, int colIndex, int firstRow, int lastRow, int firstCol, int lastCol, IFont font, short dataFormat = 0)
    {
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.AddMergedRegion(region);

        ICell cell = GetCell(row, colIndex);
        cell.CellStyle.SetFont(font);
        cell.CellStyle.Alignment = HorizontalAlignment.CENTER;
        cell.CellStyle.DataFormat = dataFormat;

        return cell;
    }

    //Set Region Style
    private void SetRegionStyle(ISheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, ICellStyle cellTyle)
    {
        for (int i = firstRow; i <= lastRow; i++)
        {
            IRow r = NpoiHelper.GetRow(sheet, i);

            for (int j = firstCol; j <= lastCol; j++)
            {
                ICell c = NpoiHelper.GetCell(r, j);
                c.CellStyle = cellTyle;
            }
        }
    }

    //Get Cell With Style
    public ICell GetCellWithStyle(IRow row, int colIndex, ICellStyle style)
    {
        ICell cell = NpoiHelper.GetCell(row, colIndex);
        cell.CellStyle = style;
        
        return cell;
    }

    //Get Report Header Cell
    public ICell GetReportHeader(IRow row, int colIndex)
    {
        return GetCellWithStyle(row, colIndex, this.ReportHeaderStyle);
    }

    //Get Report Header Cell & Merged Region
    public ICell GetReportHeader(ISheet sheet, IRow row, int colIndex, int firstRow, int lastRow, int firstCol, int lastCol)
    {
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.AddMergedRegion(region);

        return GetReportHeader(row, colIndex);
    }

    //Get Report Header Right Cell
    public ICell GetReportHeaderRight(IRow row, int colIndex)
    {
        return GetCellWithStyle(row, colIndex, this.ReportHeaderRightStyle);
    }

    //Get Report Header Right Cell & Merged Region
    public ICell GetReportHeaderRight(ISheet sheet, IRow row, int colIndex, int firstRow, int lastRow, int firstCol, int lastCol)
    {
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.AddMergedRegion(region);

        return GetReportHeaderRight(row, colIndex);
    }

    //Get Report Header Center Cell
    public ICell GetReportHeaderCenter(IRow row, int colIndex)
    {
        return GetCellWithStyle(row, colIndex, this.ReportrHeaderCenteStyle);
    }

    //Get Report Header Center Cell & Merged Region
    public ICell GetReportHeaderCellCenter(ISheet sheet, IRow row, int colIndex, int firstRow, int lastRow, int firstCol, int lastCol)
    {
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.AddMergedRegion(region);

        return GetReportHeaderCenter(row, colIndex);
    }

    //Get Report Title Cell
    public ICell GetReportTitle(IRow row, int colIndex)
    {
        return GetCellWithStyle(row, colIndex, this.ReportTitleStyle);
    }

    //Get Report Title Cell & Merged Region
    public ICell GetReportTitle(ISheet sheet, IRow row, int colIndex, int firstRow, int lastRow, int firstCol, int lastCol)
    {
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.AddMergedRegion(region);

        return GetReportTitle(row, colIndex);
    }

    //Get Report Title Parameter Cell
    public ICell GetReportTitleParam(IRow row, int colIndex)
    {
        return GetCellWithStyle(row, colIndex, this.ReportTitleParamStyle);
    }

    //Get Report Title Parameter Center Cell
    public ICell GetReportTitleParamCenter(IRow row, int colIndex)
    {
        return GetCellWithStyle(row, colIndex, this.ReportTitleParamCenterStyle);
    }

    //Get Report Title Parameter Right Cell
    public ICell GetReportTitleParamRight(IRow row, int colIndex)
    {
        return GetCellWithStyle(row, colIndex, this.ReportTitleParamRightStyle);
    }

    //Get Report Title Parameter Cell & Merged Region
    public ICell GetReportTitleParam(ISheet sheet, IRow row, int colIndex, int firstRow, int lastRow, int firstCol, int lastCol)
    {
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.AddMergedRegion(region);

        return GetReportTitleParam(row, colIndex);
    }

    //Get Report Title Parameter Cell & Merged Region
    public ICell GetReportTitleParamCenter(ISheet sheet, IRow row, int colIndex, int firstRow, int lastRow, int firstCol, int lastCol)
    {
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.AddMergedRegion(region);

        return GetReportTitleParamCenter(row, colIndex);
    }

    //Get Report Title Parameter Cell & Merged Region
    public ICell GetReportTitleParamRight(ISheet sheet, IRow row, int colIndex, int firstRow, int lastRow, int firstCol, int lastCol)
    {
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.AddMergedRegion(region);

        return GetReportTitleParamRight(row, colIndex);
    }

    //Get Italic Cell
    public ICell GetItalicCell(IRow row, int colIndex)
    {
        return GetCellWithStyle(row, colIndex, this.ItalicStyle);
    }

    //Get Italic Center Cell
    public ICell GetItalicCenterCell(IRow row, int colIndex)
    {
        return GetCellWithStyle(row, colIndex, this.ItalicCenterStyle);
    }

    //Get Italic Right Cell
    public ICell GetItalicRightCell(IRow row, int colIndex)
    {
        return GetCellWithStyle(row, colIndex, this.ItalicRightStyle);
    }

    //Get Italic Style & Merged Region
    public ICell GetItalicCell(ISheet sheet, IRow row, int colIndex, int firstRow, int lastRow, int firstCol, int lastCol)
    {
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.AddMergedRegion(region);

        return GetItalicCell(row, colIndex);
    }

    //Get Italic Center Style & Merged Region
    public ICell GetItalicCenterCell(ISheet sheet, IRow row, int colIndex, int firstRow, int lastRow, int firstCol, int lastCol)
    {
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.AddMergedRegion(region);

        return GetItalicCenterCell(row, colIndex);
    }

    //Get Italic Right Style & Merged Region
    public ICell GetItalicRightCell(ISheet sheet, IRow row, int colIndex, int firstRow, int lastRow, int firstCol, int lastCol)
    {
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.AddMergedRegion(region);

        return GetItalicRightCell(row, colIndex);
    }

    //Get Table Header Cell
    public ICell GetTableHeader(IRow row, int colIndex)
    {
        return GetCellWithStyle(row, colIndex, this.TableHeaderStyle);
    }
    public ICell GetTableHeaderWarp(IRow row, int colIndex)
    {
        return GetCellWithStyle(row, colIndex, this.StyleWarp);
    }
    public ICell GetTableHeaderWarp(ISheet sheet, IRow row, int colIndex, int firstRow, int lastRow, int firstCol, int lastCol)
    {
        SetRegionStyle(sheet, firstRow, lastRow, firstCol, lastCol, this.StyleWarp);

        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.AddMergedRegion(region);
        return GetTableHeaderWarp(row, colIndex);
    }
    //Get TableHeaderCell & Merged Region
    public ICell GetTableHeader(ISheet sheet, IRow row, int colIndex, int firstRow, int lastRow, int firstCol, int lastCol)
    {
        SetRegionStyle(sheet, firstRow, lastRow, firstCol, lastCol, this.TableHeaderStyle);

        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.AddMergedRegion(region);

        return GetTableHeader(row, colIndex);
    }
    //Get Table Header Center Cell
    public ICell GetTableHeaderCenter(IRow row, int colIndex)
    {
        return GetCellWithStyle(row, colIndex, this.TableHeaderCenterStyle);
    }

    //Get TableHeaderCenterCell & Merged Region
    public ICell GetTableHeaderCenter(ISheet sheet, IRow row, int colIndex, int firstRow, int lastRow, int firstCol, int lastCol)
    {
        SetRegionStyle(sheet, firstRow, lastRow, firstCol, lastCol, this.TableHeaderStyle);

        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.AddMergedRegion(region);

        return GetTableHeaderCenter(row, colIndex);
    }

    //Get Table Header Right Cell
    public ICell GetTableHeaderRight(IRow row, int colIndex)
    {
        return GetCellWithStyle(row, colIndex, this.TableHeaderRightStyle);
    }

    //Get TableHeaderRightCell & Merged Region
    public ICell GetTableHeaderRight(ISheet sheet, IRow row, int colIndex, int firstRow, int lastRow, int firstCol, int lastCol)
    {
        SetRegionStyle(sheet, firstRow, lastRow, firstCol, lastCol, this.TableHeaderStyle);

        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.AddMergedRegion(region);

        return GetTableHeaderRight(row, colIndex);
    }

    //Get TableCell
    public ICell GetTableCell(IRow row, int colIndex)
    {
        return GetCellWithStyle(row, colIndex, this.TableCellStyle);
    }
    public ICell GetTableCellWrap(IRow row, int colIndex)
    {
        return GetCellWithStyle(row, colIndex, this.StyleWarp);
    }
    //Get TableCell & Merged Region
    public ICell GetTableCell(ISheet sheet, IRow row, int colIndex, int firstRow, int lastRow, int firstCol, int lastCol)
    {
        SetRegionStyle(sheet, firstRow, lastRow, firstCol, lastCol, this.TableCellStyle);

        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.AddMergedRegion(region);

        return GetTableCell(row, colIndex);
    }

    //Get Table CellRight
    public ICell GetTableCellRight(IRow row, int colIndex)
    {
        return GetCellWithStyle(row, colIndex, this.TableCellRightStyle);
    }

    //Get TableCellRight & Merged Region
    public ICell GetTableCellRight(ISheet sheet, IRow row, int colIndex, int firstRow, int lastRow, int firstCol, int lastCol)
    {
        SetRegionStyle(sheet, firstRow, lastRow, firstCol, lastCol, this.TableCellRightStyle);

        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.AddMergedRegion(region);

        return GetTableCellRight(row, colIndex);
    }

    //Get TableCellCenter
    public ICell GetTableCellCenter(IRow row, int colIndex)
    {
        return GetCellWithStyle(row, colIndex, this.TableCellCenterStyle);
    }

    //Get TableCellCenter & Merged Region
    public ICell GetTableCellCenter(ISheet sheet, IRow row, int colIndex, int firstRow, int lastRow, int firstCol, int lastCol)
    {
        SetRegionStyle(sheet, firstRow, lastRow, firstCol, lastCol, this.TableCellCenterStyle);

        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.AddMergedRegion(region);

        return GetTableCellCenter(row, colIndex);
    }

    //Get NormalCell
    public ICell GetNormalCell(IRow row, int colIndex)
    {
        return GetCellWithStyle(row, colIndex, this.NormalStyle);
    }

    //Get NormalCell & Merged Region
    public ICell GetNormalCell(ISheet sheet, IRow row, int colIndex, int firstRow, int lastRow, int firstCol, int lastCol)
    {
        SetRegionStyle(sheet, firstRow, lastRow, firstCol, lastCol, this.NormalStyle);

        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.AddMergedRegion(region);

        return GetNormalCell(row, colIndex);
    }

    //Get Normal CellRight
    public ICell GetNormalRightCell(IRow row, int colIndex)
    {
        return GetCellWithStyle(row, colIndex, this.NormalRightStyle);
    }

    //Get NormalCellRight & Merged Region
    public ICell GetNormalRightCell(ISheet sheet, IRow row, int colIndex, int firstRow, int lastRow, int firstCol, int lastCol)
    {
        SetRegionStyle(sheet, firstRow, lastRow, firstCol, lastCol, this.NormalRightStyle);

        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.AddMergedRegion(region);

        return GetNormalRightCell(row, colIndex);
    }

    //Get NormalCellCenter
    public ICell GetNormalCenterCell(IRow row, int colIndex)
    {
        return GetCellWithStyle(row, colIndex, this.NormalCenterStyle);
    }

    //Get NormalCellCenter & Merged Region
    public ICell GetNormalCenterCell(ISheet sheet, IRow row, int colIndex, int firstRow, int lastRow, int firstCol, int lastCol)
    {
        SetRegionStyle(sheet, firstRow, lastRow, firstCol, lastCol, this.NormalCenterStyle);

        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.AddMergedRegion(region);

        return GetNormalCenterCell(row, colIndex);
    }

    //Draw Border of Region
    public void DrawBorder(ISheet sheet, int firstRow, int lastRow, int firstCol, int lastCol)
    {
        for (int i = firstRow; i <= lastRow; i++)
        {
            IRow r = NpoiHelper.GetRow(sheet, i);

            for (int j = firstCol; j <= lastCol; j++)
            {
                ICell c = NpoiHelper.GetCell(r, j);

                if (firstRow == lastRow && firstCol == lastCol)
                {
                    c.CellStyle = this.BorderAllStyle;
                }
                else if (firstRow == lastRow)
                {
                    if (j == firstCol)
                    {
                        c.CellStyle = BorderLeftTopBottomStyle;
                    }
                    else if (j == lastCol)
                    {
                        c.CellStyle = BorderRightTopBottomStyle;
                    }
                    else
                    {
                        c.CellStyle = BorderTopBottomStyle;
                    }
                }
                else if (firstCol == lastCol)
                {
                    if (i == firstRow)
                    {
                        c.CellStyle = BorderTopLeftRightStyle;
                    }
                    else if (i == lastRow)
                    {
                        c.CellStyle = BorderBottomLeftRightStyle;
                    }
                    else
                    {
                        c.CellStyle = BorderLeftRightStyle;
                    }
                }
                else if (i == firstRow && j == firstCol)
                {
                    c.CellStyle = this.BorderTopLeftStyle;
                }
                else if (i == firstRow && j == lastCol)
                {
                    c.CellStyle = this.BorderTopRightStyle;
                }
                else if (i == lastRow && j == firstCol)
                {
                    c.CellStyle = this.BorderBottomLeftStyle;
                }
                else if (i == lastRow && j == lastCol)
                {
                    c.CellStyle = this.BorderBottomRightStyle;
                }
                else if (i == firstRow)
                {
                    c.CellStyle = this.BorderTopStyle;
                }
                else if (i == lastRow)
                {
                    c.CellStyle = this.BorderBottomStyle;
                }
                else if (j == firstCol)
                {
                    c.CellStyle = this.BorderLeftStyle;
                }
                else if (j == lastCol)
                {
                    c.CellStyle = this.BorderRightStyle;
                }
            }
        }
    }
}